{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "sys.path.append('..')\n",
    "from configure.settings import DBSelector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = DBSelector().get_engine('db_reits','tencent-1c')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql('reits_history',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>volume</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>chg</th>\n",
       "      <th>percent</th>\n",
       "      <th>turnoverrate</th>\n",
       "      <th>amount</th>\n",
       "      <th>code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-10-10</td>\n",
       "      <td>32944545</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>0.657</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93793119.0</td>\n",
       "      <td>SZ180102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2022-10-11</td>\n",
       "      <td>46590865</td>\n",
       "      <td>2.902</td>\n",
       "      <td>2.902</td>\n",
       "      <td>2.656</td>\n",
       "      <td>2.665</td>\n",
       "      <td>-0.182</td>\n",
       "      <td>-6.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>127474999.0</td>\n",
       "      <td>SZ180102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-10-12</td>\n",
       "      <td>18922379</td>\n",
       "      <td>2.665</td>\n",
       "      <td>2.735</td>\n",
       "      <td>2.613</td>\n",
       "      <td>2.719</td>\n",
       "      <td>0.054</td>\n",
       "      <td>2.03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50817384.0</td>\n",
       "      <td>SZ180102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2022-10-13</td>\n",
       "      <td>8198337</td>\n",
       "      <td>2.704</td>\n",
       "      <td>2.729</td>\n",
       "      <td>2.680</td>\n",
       "      <td>2.710</td>\n",
       "      <td>-0.009</td>\n",
       "      <td>-0.33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22163622.0</td>\n",
       "      <td>SZ180102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>2022-10-14</td>\n",
       "      <td>10037875</td>\n",
       "      <td>2.703</td>\n",
       "      <td>2.713</td>\n",
       "      <td>2.661</td>\n",
       "      <td>2.673</td>\n",
       "      <td>-0.037</td>\n",
       "      <td>-1.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27018560.0</td>\n",
       "      <td>SZ180102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id        date    volume   open   high    low  close    chg  percent  \\\n",
       "0  1.0  2022-10-10  32944545  2.847  2.847  2.847  2.847  0.657    30.00   \n",
       "1  2.0  2022-10-11  46590865  2.902  2.902  2.656  2.665 -0.182    -6.39   \n",
       "2  3.0  2022-10-12  18922379  2.665  2.735  2.613  2.719  0.054     2.03   \n",
       "3  7.0  2022-10-13   8198337  2.704  2.729  2.680  2.710 -0.009    -0.33   \n",
       "4  8.0  2022-10-14  10037875  2.703  2.713  2.661  2.673 -0.037    -1.37   \n",
       "\n",
       "   turnoverrate       amount      code  \n",
       "0           0.0   93793119.0  SZ180102  \n",
       "1           0.0  127474999.0  SZ180102  \n",
       "2           0.0   50817384.0  SZ180102  \n",
       "3           0.0   22163622.0  SZ180102  \n",
       "4           0.0   27018560.0  SZ180102  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "name_df = pd.read_sql('reits_name_mapper',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_df = pd.merge(df,name_df,on='code',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>volume</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>chg</th>\n",
       "      <th>percent</th>\n",
       "      <th>turnoverrate</th>\n",
       "      <th>amount</th>\n",
       "      <th>code</th>\n",
       "      <th>index</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-10-10</td>\n",
       "      <td>32944545</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>2.847</td>\n",
       "      <td>0.657</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93793119.0</td>\n",
       "      <td>SZ180102</td>\n",
       "      <td>19</td>\n",
       "      <td>华夏合肥高新REIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2022-10-11</td>\n",
       "      <td>46590865</td>\n",
       "      <td>2.902</td>\n",
       "      <td>2.902</td>\n",
       "      <td>2.656</td>\n",
       "      <td>2.665</td>\n",
       "      <td>-0.182</td>\n",
       "      <td>-6.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>127474999.0</td>\n",
       "      <td>SZ180102</td>\n",
       "      <td>19</td>\n",
       "      <td>华夏合肥高新REIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-10-12</td>\n",
       "      <td>18922379</td>\n",
       "      <td>2.665</td>\n",
       "      <td>2.735</td>\n",
       "      <td>2.613</td>\n",
       "      <td>2.719</td>\n",
       "      <td>0.054</td>\n",
       "      <td>2.03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50817384.0</td>\n",
       "      <td>SZ180102</td>\n",
       "      <td>19</td>\n",
       "      <td>华夏合肥高新REIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2022-10-13</td>\n",
       "      <td>8198337</td>\n",
       "      <td>2.704</td>\n",
       "      <td>2.729</td>\n",
       "      <td>2.680</td>\n",
       "      <td>2.710</td>\n",
       "      <td>-0.009</td>\n",
       "      <td>-0.33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22163622.0</td>\n",
       "      <td>SZ180102</td>\n",
       "      <td>19</td>\n",
       "      <td>华夏合肥高新REIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>2022-10-14</td>\n",
       "      <td>10037875</td>\n",
       "      <td>2.703</td>\n",
       "      <td>2.713</td>\n",
       "      <td>2.661</td>\n",
       "      <td>2.673</td>\n",
       "      <td>-0.037</td>\n",
       "      <td>-1.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27018560.0</td>\n",
       "      <td>SZ180102</td>\n",
       "      <td>19</td>\n",
       "      <td>华夏合肥高新REIT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id        date    volume   open   high    low  close    chg  percent  \\\n",
       "0  1.0  2022-10-10  32944545  2.847  2.847  2.847  2.847  0.657    30.00   \n",
       "1  2.0  2022-10-11  46590865  2.902  2.902  2.656  2.665 -0.182    -6.39   \n",
       "2  3.0  2022-10-12  18922379  2.665  2.735  2.613  2.719  0.054     2.03   \n",
       "3  7.0  2022-10-13   8198337  2.704  2.729  2.680  2.710 -0.009    -0.33   \n",
       "4  8.0  2022-10-14  10037875  2.703  2.713  2.661  2.673 -0.037    -1.37   \n",
       "\n",
       "   turnoverrate       amount      code  index        name  \n",
       "0           0.0   93793119.0  SZ180102     19  华夏合肥高新REIT  \n",
       "1           0.0  127474999.0  SZ180102     19  华夏合肥高新REIT  \n",
       "2           0.0   50817384.0  SZ180102     19  华夏合肥高新REIT  \n",
       "3           0.0   22163622.0  SZ180102     19  华夏合肥高新REIT  \n",
       "4           0.0   27018560.0  SZ180102     19  华夏合肥高新REIT  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "result=[]\n",
    "for code,sub_df in all_df.groupby('code'):\n",
    "    sub_df['date']=pd.to_datetime(sub_df['date'],format='%Y-%m-%d')\n",
    "    sub_df = sub_df.set_index('date',drop=True)\n",
    "    # print(code,sub_df.index[0])\n",
    "    tmp_dict = {}\n",
    "    p = (sub_df['close'].iloc[-1] - sub_df['close'].iloc[0])/sub_df['close'].iloc[0]*100\n",
    "    tmp_dict['code']=code\n",
    "    tmp_dict['percent']=p\n",
    "    tmp_dict['first_percent']=round(sub_df['percent'].iloc[0],2)\n",
    "    tmp_dict['start']=sub_df.index[0]\n",
    "    result.append(tmp_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.sort_values('percent',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_df = pd.merge(df,name_df,on='code',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_df.to_excel('reits.xlsx',encoding='utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " "
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "906064a35c7980fda78a8b97014bc5560dca56efd2e047844bdf4fb8a9b9c8a3"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('cpy')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
